﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BI.CF.PageList;
using BI.CF.Model;
using BI.CF.IDAL;
using System.Data.SqlClient;
using System.Data;
namespace BI.CF.DAL
{
    public class Invest_DAL : InvestIDAL
    {
        /// <summary>
        /// 显示+搜索+分页（我要投资）
        /// </summary>
        /// <param name="pageindex">当前页</param>
        /// <param name="pagesize">当前页显示条数</param>
        /// <param name="huankuantype">还款方式</param>
        /// <param name="jiekuandaystar">借款期限的开始天数</param>
        /// <param name="jiekuandayend">借款期限的结束天数</param>
        /// <param name="biaotype">标的类型</param>
        /// <param name="paixu">结果的排序</param>
        /// <returns></returns>
        public Pager<MemLoanModel> Invest_Show(int pageindex, int pagesize, string huankuantype, int jiekuandaystar, int jiekuandayend, string biaotype, string paixu)
        {
            SqlParameter[] parameter ={
                                          new SqlParameter("@pageindex",SqlDbType.Int){Value=pageindex},
                                          new SqlParameter("@pagesize",SqlDbType.Int){Value=pagesize},
                                          new SqlParameter("@huankuantype",SqlDbType.VarChar){Value=huankuantype},
                                          new SqlParameter("@jiekuanqixianstar",SqlDbType.Int){Value=jiekuandaystar},
                                          new SqlParameter("@jiekuanqixianend",SqlDbType.Int){Value=jiekuandayend},
                                          new SqlParameter("@biaotype",SqlDbType.VarChar){Value=biaotype},
                                          new SqlParameter("@paixu",SqlDbType.VarChar){Value=paixu}
                                      };
            int coutout;
            DataTable read = DbHelperSQL.RunProcedure("PROC_Invest_Show", parameter, pagesize, pageindex, out coutout);//coutout是一个输出值
            Pager<MemLoanModel> plist = new Pager<MemLoanModel>();
            List<MemLoanModel> list = new List<MemLoanModel>();
            for (int i = 0; i < read.Rows.Count; i++)
            {
                MemLoanModel m = new MemLoanModel();
                m.MemLoanId = Convert.ToInt32(read.Rows[i]["MemLoanId"]);
                m.headimg = read.Rows[i]["HeadImage"].ToString();
                m.UserName = read.Rows[i]["UserName"].ToString();
                m.Title = read.Rows[i]["Title"].ToString();
                m.LoanMoney = Convert.ToDecimal(read.Rows[i]["LoanMoney"].ToString());
                m.RepaymentModeId = read.Rows[i]["RepaymentModeId"].ToString();
                m.LoanTerm = Convert.ToInt32(read.Rows[i]["LoanTerm"].ToString());
                m.ExamineState = read.Rows[i]["ExamineState"].ToString();
                m.EndLoanTime = DateTime.Parse(read.Rows[i]["EndLoanTime"].ToString());
                m.AlreadyRaiseLoan = decimal.Parse(read.Rows[i]["AlreadyRaiseLoan"].ToString());
                m.Annualrate = float.Parse(read.Rows[i]["Annualrate"].ToString());
                m.LaunchLoanTime = DateTime.Parse(read.Rows[i]["LaunchLoanTime"].ToString());
                m.LoanExplain = read.Rows[i]["LoanExplain"].ToString();
                m.LoanPurpose = read.Rows[i]["LoanPurpose"].ToString();
                m.LoanRaise = int.Parse(read.Rows[i]["LoanRaise"].ToString());
                m.LoanType = read.Rows[i]["LoanType"].ToString();
                m.PayInterest = decimal.Parse(read.Rows[i]["PayInterest"].ToString());
                m.RepaymentDay = DateTime.Parse(read.Rows[i]["RepaymentDay"].ToString());
                m.RepaySumMoney = decimal.Parse(read.Rows[i]["RepaySumMoney"].ToString());
                m.TotalTransactions = int.Parse(read.Rows[i]["TotalTransactions"].ToString());
                m.UserrId = int.Parse(read.Rows[i]["UserrId"].ToString());
                list.Add(m);
            };
            //往pagerlist里赋值
            plist.Data = list;
            plist.PageIndex = pageindex;
            plist.PageSize = pagesize;
            if (coutout % pagesize == 0)
            {
                plist.PageCount = coutout / pagesize;
            }
            else
            {
                plist.PageCount = coutout / pagesize + 1;
            }
            return plist;
        }

        /// <summary>
        /// 通过memtable查询user表里面的userid
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserrsModel Mem_select_User_ID(int id)
        {
            SqlParameter[] parameter ={

                new SqlParameter("@Mem_userid",SqlDbType.Int){Value=id}
            };
            SqlDataReader read = DbHelperSQL.RunProcedure("PROC_Mem_select_User_ID", parameter);
            UserrsModel m = new UserrsModel();
            #region
            while (read.Read())
            {
                m.UserId = Convert.ToInt32(read["UserId"].ToString());
                m.UserName = read["UserName"].ToString();
                m.HeadImage = read["HeadImage"].ToString();
                m.IdCard = read["IdCard"].ToString();
                m.UserEmail = read["UserEmail"].ToString();
                m.UserPhone = read["UserPhone"].ToString();
                m.UserTel = read["UserTel"].ToString();
                m.MaritalState =read["MaritalState"].ToString();
                m.MIncome = Convert.ToDecimal(read["MIncome"].ToString());
                m.UserrBcardMesId = read["UserrBcardMesId"].ToString();
                m.UserrPlaceOrigin = read["UserrPlaceOrigin"].ToString();
                m.UserrAddres = read["UserrAddres"].ToString();
                m.UserrRealName = read["UserrRealName"].ToString();
                m.UserrOccupation = read["UserrOccupation"].ToString();
                m.UserAge = Convert.ToInt32(read["UserAge"].ToString());
                m.UserEducation = read["UserEducation"].ToString();
                m.Creditlimit = Convert.ToInt32(read["Creditlimit"].ToString());
                m.UserMemlevelId = Convert.ToInt32(read["UserMemlevelId"].ToString());
            }
            #endregion
            return m;
        }
        /// <summary>
        /// 进行投资的详情
        /// </summary>
        /// <returns></returns>
        public MemLoanModel Memloan_Show_XiangQing(int memid)
        {
            SqlParameter[] parameter ={
                                         new SqlParameter("@Memloanid",SqlDbType.Int){Value=memid}
                                     };
            SqlDataReader read = DbHelperSQL.RunProcedure("PROC_MemLoan_XiangQing_Show", parameter);
            MemLoanModel m = new MemLoanModel();
            while (read.Read())
            {
                m.MemLoanId = int.Parse(read["MemLoanId"].ToString());
                m.LoanMoney = decimal.Parse(read["LoanMoney"].ToString());
                #region 注释掉的以备不时之需
                //m.LoanPurpose = read["LoanPurpose"].ToString();
                // m.LoanRaise =Convert.ToInt32(read["LoanRaise"].ToString());
                // m.RepaymentDay = DateTime.Parse(read["RepaymentDay"].ToString());
                // m.PayInterest = decimal.Parse(read["PayInterest"].ToString());
                // m.RepaySumMoney = decimal.Parse(read["RepaySumMoney"].ToString());
                //m.ExamineState = read["ExamineState"].ToString();
                //m.RepaymentModeId = read["RepaymentModeId"].ToString();
                //m.LoanExplain = read["LoanExplain"].ToString();
                // m.TotalTransactions = int.Parse(read["TotalTransactions"].ToString());
                // m.LoanType = read["LoanType"].ToString();
                #endregion
                m.Annualrate = float.Parse(read["Annualrate"].ToString());
                m.LoanTerm = int.Parse(read["LoanTerm"].ToString());
                m.Title = read["Title"].ToString();
                m.UserrId = int.Parse(read["UserrId"].ToString());
                m.LoanRaise = int.Parse(read["LoanRaise"].ToString());
                m.LaunchLoanTime = DateTime.Parse(read["LaunchLoanTime"].ToString());
                m.EndLoanTime = DateTime.Parse(read["EndLoanTime"].ToString());
                m.AlreadyRaiseLoan = decimal.Parse(read["AlreadyRaiseLoan"].ToString());
            };
            return m;
        }

        /// <summary>
        /// 得出用户财产
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public MemPropertyModel Memloan_Show_Memproperty(int userid)
        {
            SqlParameter[] parameter ={
                                         new SqlParameter("@Userid",SqlDbType.Int){Value=userid}
                                     };
            SqlDataReader date = DbHelperSQL.RunProcedure("PROC_User_MemProperty", parameter);
            MemPropertyModel m = new MemPropertyModel();
            while (date.Read())
            {
                m.MemPId = Convert.ToInt32(date["MemPId"].ToString());
                m.AccountTotal = Convert.ToDecimal(date["AccountTotal"].ToString());
                m.FrozenTotal = Convert.ToDecimal(date["FrozenTotal"].ToString());
                m.AvailableBalance = Convert.ToDecimal(date["AvailableBalance"].ToString());
                m.IncomeSum = Convert.ToDecimal(date["IncomeSum"].ToString());
                m.UserrId = Convert.ToInt32(date["UserId"].ToString());
                m.UserName = date["UserName"].ToString();
            }
            return m;
        }

        /// <summary>
        /// 进行投资添加
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="TouZiMoney"></param>
        /// <param name="TouZiLiXi"></param>
        /// <param name="TouZiStarTime"></param>
        /// <param name="MemLoanID"></param>
        /// <returns></returns>
        public bool TouZi_Geting(int UserID, decimal TouZiMoney, string TouZiLiXi, string TouZiStarTime, int MemLoanID)
        {
            SqlParameter[] parameter ={
                                          new SqlParameter("@UserID",SqlDbType.Int){Value=UserID},
                                          new SqlParameter("@TouZiMoney",SqlDbType.Decimal){Value=TouZiMoney},
                                          new SqlParameter("@TouZiLiXi",SqlDbType.VarChar){Value=TouZiLiXi},
                                          new SqlParameter("@TouZiStarTime",SqlDbType.VarChar){Value=TouZiStarTime},
                                          new SqlParameter("@MemLoanID",SqlDbType.Int){Value=MemLoanID}
                                      };
            bool i = DbHelperSQL.RunProcedureReturnBool("PROC_TouZi_Get", parameter);
            SqlParameter[] parameter2 ={
                                           new SqlParameter("@Memloanid",SqlDbType.Int){Value=MemLoanID}
                                       };
            SqlDataReader r = DbHelperSQL.RunProcedure("PROC_PuanDuanTouZiShiFouManBiao", parameter2);
            decimal loanmoney = 0, chouji = 0;
            while (r.Read())
            {
               loanmoney=Convert.ToDecimal( r["LoanMoney"]);
               chouji = Convert.ToDecimal(r["AlreadyRaiseLoan"]);
            };
            if (loanmoney==chouji)
            {
                //执行满标状态
                SqlParameter[] parameter3 ={
                                              new SqlParameter("@Memloanid",SqlDbType.Int){Value=MemLoanID}
                                          };
                DbHelperSQL.RunProcedureReturnBool("PROC_UpdateMemloan_AlreadyRaiseLoan", parameter3);
            }
            return i;
        }

        /// <summary>
        /// 查询投资人的账户是否满足投资金额
        /// </summary>
        /// <param name="userid">投资人ID</param>
        /// <returns></returns>
        public decimal AvailableBalance(int userid)
        {
             SqlParameter [] parameter={
                 new SqlParameter ("@UserID",SqlDbType.Int){Value=userid}
             };
             SqlDataReader read = DbHelperSQL.RunProcedure("PROC_Select_TouZiRen_ZhangHu", parameter);
             decimal Money_TouZi = 0;
             while (read.Read())
             {
                 Money_TouZi = Convert.ToDecimal(read["AvailableBalance"]);
             }
             return Money_TouZi;
        }
    }
}
